﻿using StudySystem.MainLogic;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace StudySystem.SQLServer
{
    public static class DBForSubject
    {
        /// <summary>
        /// 获得当前用户的所有科目
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="ds"></param>
        public static void GetSubjects(string userId, ref DataSet ds)
        {
            string sql = "select s.UserID, s.SubjectID, SubjectName,count(q.SubjectID) QuesCount " +
                "from SubjectTable s left join QuestionTable q on s.SubjectID = q.SubjectID" +
                " where s.UserId = " + userId  +
                " group by s.SubjectID, SubjectName,s.UserID";
            DbHelper.GetTable(sql,ref ds, "subjectTable");
        }

        /// <summary>
        /// 在数据库中插入学科
        /// </summary>
        /// <param name="subName"></param>
        /// <returns></returns>
        public static int InsertASubject(string subName)
        {
            string sql = "insert into SubjectTable(UserID, SubjectName) " +
               "values(@UserID,@SubjectName);";
            SqlParameter paraUserID = new SqlParameter("@UserID", SqlDbType.NChar);
            paraUserID.Value = UserManagement.user.UserID;
            SqlParameter paraSubName = new SqlParameter("@SubjectName", SqlDbType.NVarChar);
            paraSubName.Value = subName;

            return DbHelper.SafeExecuteNonQuery(sql, paraUserID, paraSubName);
        }

        /// <summary>
        /// 级联删除当前学科所有问题及学科信息
        /// </summary>
        /// <param name="subjectID"></param>
        /// <returns></returns>
        public static int DeleteASubject(string subjectID)
        {
            string sql = "delete from RecordTable where subjectID = " + subjectID + ";" +
                "delete from QuestionTable where subjectID = " + subjectID + "; " +
                "delete from SubjectTable where subjectID =" + subjectID + ";";
            return DbHelper.ModifyRecord(sql);
        }

        public static int ModifySubName(string subjectID, string newName)
        {
            string sql = "update SubjectTable set SubjectName = @paraSubName where SubjectID = @paraSubID";
            SqlParameter paraSubName = new SqlParameter("@paraSubName", SqlDbType.NVarChar);
            paraSubName.Value = newName;
            SqlParameter paraSubID = new SqlParameter("@paraSubID", SqlDbType.Int);
            paraSubID.Value = subjectID;
            return DbHelper.SafeExecuteNonQuery(sql, paraSubName, paraSubID);
        }

    }
}
